# 导入数据请求模块
import requests
# 导入格式化输出模块
from pprint import pprint
# 导入数据库
import pymysql
from pyecharts import options as opts
from pyecharts.charts import Bar, Pie, Page

# 1.发送请求

# 模拟浏览器
headers={
'Accept':'application/json, text/plain, */*',
'Accept-Encoding':'gzip, deflate, br, zstd',
'Accept-Language':'zh-CN,zh;q=0.9',
'Connection':'keep-alive',
'Content-Length':'439',
'Content-Type':'application/json;charset=UTF-8;',
'Cookie':'XSRF-TOKEN=nACV8nafQ6CL9F93Z6VBdQ; __gc_id=2b26e87ef9ae4220b19159bcd6486001; __uuid=1746239612089.32; __tlog=1746239612106.06%7C00000000%7C00000000%7Cs_o_007%7Cs_o_007; Hm_lvt_a2647413544f5a04f00da7eee0d5e200=1746239612; HMACCOUNT=18F6AEB69B045629; acw_tc=ac11000117462396123517304e006734b70fa3803fe684db7aaee27fb30f42; Hm_lpvt_a2647413544f5a04f00da7eee0d5e200=1746239620; __session_seq=4; __tlg_event_seq=52',
'Host':'api-c.liepin.com',
'Origin':'https://www.liepin.com',
'Referer':'https://www.liepin.com/',
'Sec-Ch-Ua':'"Chromium";v="9", "Not?A_Brand";v="8"',
'Sec-Ch-Ua-Mobile':'?0',
'Sec-Ch-Ua-Platform':'"Windows"',
'Sec-Fetch-Dest':'empty',
'Sec-Fetch-Mode':'cors',
'Sec-Fetch-Site':'same-site',
'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36 SLBrowser/9.0.6.2081 SLBChan/109 SLBVPV/64-bit',
'X-Client-Type':'web',
'X-Fscp-Bi-Stat':'{"location": "https://www.liepin.com/zhaopin/?inputFrom=www_index&workYearCode=0&key=java&scene=input&ckId=zu6cbcyegx26jrds5mnnl98r5fa7jvxb&dq="}',
'X-Fscp-Fe-Version': '',
'X-Fscp-Std-Info':'{"client_id": "40108"}',
'X-Fscp-Trace-Id':'bf8971bb-1e54-4ef3-b3d5-10001698bd7a',
'X-Fscp-Version':'1.1',
'X-Requested-With':'XMLHttpRequest',
'X-Xsrf-Token':'nACV8nafQ6CL9F93Z6VBdQ'
}

# url地址(请求网址)
url='https://api-c.liepin.com/api/com.liepin.searchfront4c.pc-search-job'
# 请求参数
data={"data":{"mainSearchPcConditionForm":{"city":"410","dq":"410","pubTime":"","currentPage":0,"pageSize":40,"key":"java","suggestTag":"","workYearCode":"0","compId":"","compName":"","compTag":"","industry":"","salaryCode":"","jobKind":"","compScale":"","compKind":"","compStage":"","eduLevel":"","salaryLow":"","salaryHigh":""},"passThroughForm":{"scene":"input","skId":"","fkId":"","ckId":"oq64ei0ofy8bpbkjdp7cx2jgb61d5o28","suggest":None}}}
# 发送请求
response=requests.post(url=url,json=data,headers=headers)

print(f"响应状态码: {response.status_code}")
print(f"响应内容长度: {len(response.text)}")
# 2.获取数据  获取服务器返回响应数据

json_data = response.json()
print(json_data)

# 3.解析数据  提取需要的信息
# 提取职位信息所在列表
jobCardList=json_data['data']['data']['jobCardList']


# 4.连接数据库

connect=pymysql.connect(host='localhost',
                   user='root',
                   password='123456@',
                   port=3306,
                   database='liepin',
                   charset='utf8mb4')


# 创建游标对象
cursor=connect.cursor()

# 创建数据表
create_table_sql = """
CREATE TABLE IF NOT EXISTS liepin_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_title VARCHAR(255),
    city VARCHAR(255),
    salary VARCHAR(255),
    company_name VARCHAR(255),
    industry VARCHAR(255)
);
"""
cursor.execute(create_table_sql)
connect.commit()

# 插入数据到数据库
insert_sql = "INSERT INTO liepin_jobs (job_title, city, salary, company_name, industry) VALUES (%s, %s, %s, %s, %s)"
for index, item in enumerate(jobCardList):
    try:
        job = item['job']
        comp = item['comp']
        dit = {
            '职位': job.get('title', '未知'),
            '城市': job.get('dq', '未知'),
            '薪资': job.get('salary', '未知'),
            '公司': comp.get('compName', '未知'),
            '领域': comp.get('compIndustry', '未知')
        }
        print(f"要插入的数据: {dit}")
        check_sql = "SELECT 1 FROM liepin_jobs WHERE job_title = %s AND company_name = %s"
        cursor.execute(check_sql, (dit['职位'], dit['公司']))
        result = cursor.fetchone()
        print(f"去重查询结果: {result}")
        if not result:
            try:
                cursor.execute(insert_sql, (dit['职位'], dit['城市'], dit['薪资'], dit['公司'], dit['领域']))
                connect.commit()
            except pymysql.Error as e:
                print(f"插入数据时出现数据库错误: {e}")
                connect.rollback()

        # 心跳机制
        if index % 10 == 0:
            try:
                cursor.execute("SELECT 1")
            except pymysql.Error as e:
                print(f"数据库连接出现问题: {e}")
                connect = pymysql.connect(
                    host='localhost',
                    user='root',
                    password='123456@',
                    port=3306,
                    database='liepin',
                    charset='utf8mb4'
                )
                cursor = connect.cursor()
    except KeyError as e:
        print(f"解析数据时出错，缺少键: {e}，当前数据: {item}")

# 关闭数据库连接
cursor.close()
connect.close()


# 5.利用pyecharts图表库实现数据可视化

# 连接数据库
def connect_to_database():

    try:
        # 使用 pymysql 连接数据库，需要提供数据库的主机地址、用户名、密码、端口和数据库名
        connect = pymysql.connect(
            host='localhost',
            user='root',
            password='123456@',
            port=3306,
            database='liepin'
        )
        # 创建游标对象，用于执行 SQL 查询
        cursor = connect.cursor()
        return connect, cursor
    except pymysql.Error as e:
        print(f"数据库连接出错: {e}")
        return None, None

# 查询不同城市的招聘数量
def get_city_job_count(cursor):

    # 执行 SQL 查询，统计每个城市的招聘职位数量
    query_sql = "SELECT city, COUNT(*) as job_count FROM liepin_jobs GROUP BY city"
    cursor.execute(query_sql)
    results = cursor.fetchall()
    # 提取城市名称
    cities = [row[0] for row in results]
    # 提取招聘数量
    job_counts = [row[1] for row in results]
    return cities, job_counts

# 查询不同薪资范围的职位占比
def get_salary_distribution(cursor):

    # 定义薪资范围
    salary_ranges = ["0-10k", "10-20k", "20-30k", "30k+"]
    salary_data = []
    for salary_range in salary_ranges:
        if salary_range == "0-10k":
            # 统计 0-10k 薪资范围的职位数量
            query_sql = "SELECT COUNT(*) FROM liepin_jobs WHERE salary REGEXP '^[0-9]-10k'"
        elif salary_range == "10-20k":
            # 统计 10-20k 薪资范围的职位数量
            query_sql = "SELECT COUNT(*) FROM liepin_jobs WHERE salary REGEXP '^1[0-9]-20k'"
        elif salary_range == "20-30k":
            # 统计 20-30k 薪资范围的职位数量
            query_sql = "SELECT COUNT(*) FROM liepin_jobs WHERE salary REGEXP '^2[0-9]-30k'"
        else:
            # 统计 30k 以上薪资范围的职位数量
            query_sql = "SELECT COUNT(*) FROM liepin_jobs WHERE salary REGEXP '^3[0-9]k' OR salary REGEXP '^[4-9][0-9]k'"
        cursor.execute(query_sql)
        count = cursor.fetchone()[0]
        salary_data.append([salary_range, count])
    return salary_data

# 创建不同城市招聘数量的柱状图
def create_city_job_count_bar(cities, job_counts):

    bar = (
        Bar()
        # 添加 x 轴数据，即城市名称
       .add_xaxis(cities)
        # 添加 y 轴数据，即招聘数量，并设置系列名称
       .add_yaxis("招聘职位数量", job_counts)
        # 设置全局选项，包括标题、坐标轴标签、工具栏等
       .set_global_opts(
            title_opts=opts.TitleOpts(title="不同城市的招聘职位数量", subtitle="基于猎聘网 Java 职位数据"),
            xaxis_opts=opts.AxisOpts(name="城市名称", axislabel_opts=opts.LabelOpts(rotate=45)),
            yaxis_opts=opts.AxisOpts(name="招聘职位数量"),
            toolbox_opts=opts.ToolboxOpts(is_show=True)
        )
    )
    return bar

# 创建不同薪资范围职位占比的饼图
def create_salary_distribution_pie(salary_data):

    pie = (
        Pie()
        # 添加数据，设置系列名称和数据对
       .add(
            "薪资分布",
            salary_data,
            radius=["30%", "75%"],
        )
        # 设置全局选项，包括标题和图例
       .set_global_opts(
            title_opts=opts.TitleOpts(title="不同薪资范围的职位占比", subtitle="基于猎聘网 Java 职位数据"),
            legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
            toolbox_opts=opts.ToolboxOpts(is_show=True)
        )
        # 设置系列选项，包括标签格式
       .set_series_opts(
            label_opts=opts.LabelOpts(formatter="{b}: {c} ({d}%)")
        )
    )
    return pie

# 主函数，用于整合所有操作
def main():
    # 连接数据库
    connect, cursor = connect_to_database()
    if connect and cursor:
        # 获取不同城市的招聘数量
        cities, job_counts = get_city_job_count(cursor)
        # 获取不同薪资范围的职位占比
        salary_data = get_salary_distribution(cursor)

        # 创建柱状图
        city_job_count_bar = create_city_job_count_bar(cities, job_counts)
        # 创建饼图
        salary_distribution_pie = create_salary_distribution_pie(salary_data)

        # 创建 Page 对象，用于将多个图表整合到一个 HTML 页面中
        page = Page()
        page.add(city_job_count_bar)
        page.add(salary_distribution_pie)

        # 渲染页面为 HTML 文件
        page.render("recruitment_data_visualization.html")



if __name__ == "__main__":
    main()